package DBLayer;

import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Customer;
import java.sql.*;


public class DBCustomer implements IFDBCustomer {
	private Connection con;
	//creates a new instance of DBCustomer
	public DBCustomer(){
		con = DbConnection.getInstance().getDBcon();
	}
	//Implements the methods from the interface
	//get all customers
	public ArrayList<Customer> getAllCustomers(boolean retriveAssociation){
		return miscWhere("", retriveAssociation);
	}
	//get customer 
    public Customer findCustomer(int customerID, boolean retriveAssociation){
    	String wClause = "  customerID = '" + customerID + "'";
        return singleWhere(wClause, retriveAssociation);
    }
	// find one customer having the "name"
    public Customer searchCustomerName(String attValue, boolean retriveAssociation){
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    //insert a new customer
    public void insertCustomer(Customer cu) throws Exception{
    	//call to get the next customerID
        int nextId = GetMax.getMaxId("Select max(customerID) from customer");
        nextId = nextId + 1;
        System.out.println("next customerID = " +  nextId);
  
//       int rc = -1;
       
       //INSERT 
	   String query="INSERT INTO Customer(customerID, name, cAddress, zipcode, city, phoneNo)  VALUES('"+
	  		cu.getCustomerID()  + "','"  +
			cu.getName()  + "','"  +
		    cu.getcAddress()   + "','"  +
            cu.getZipCode()+ "','"  +
		    cu.getCity()+ "','"  +
            cu.getPhoneNo()+ "')";
            
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
    	  stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Customer ikke oprettet");
          throw new Exception ("Customer is not inserted correct");
       }
//       return(rc);
    }
    // updates customer
	public int updateCustomer(Customer cu){
		Customer cuObj  = cu;
		int rc=-1;

		String query="UPDATE Customer SET "+
		 	  "customerID ='"+ cuObj.getCustomerID()+"', "+
		 	  "name ='"+ cuObj.getName()+ "', " +
                          "cAddress ='"+ cuObj.getcAddress() + "', " +
                          "zipcode ='"+ cuObj.getZipCode()+ "' " +
                          "city ='"+ cuObj.getCity()+ "' " +
		          " phoneNo = '"+ cuObj.getPhoneNo() + "'";
                System.out.println("Update query:" + query);
  		try{ // update Customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	public int delete(String customerID){
               int rc=-1;
	  
	  	String query="DELETE FROM customer WHERE customerID = '" +
				customerID + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one customer
	
	 
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the customer from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
			Customer cuObj = new Customer();
		 cuObj = buildCustomer(results);	
                 list.add(cuObj);	
		}//end while
                 stmt.close();     
//                 if(retrieveAssociation)
//                 {   //The supervisor and department is to be build as well
//                     for(Customer cuObj : list){
//                        String superssn = cuObj.getSupervisor().getSsn();
//                        Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                        empObj.setSupervisor(superEmp);
//                        System.out.println("test test");
//                        DBDepartment departObj = new DBDepartment();
//                        Department dep = departObj.findDepartment(empObj.getDept().getDnumber(), false);
//                        empObj.setDepartment(dep);
//                     }
//                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer cuObj = new Customer();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            cuObj = buildCustomer(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("test2 tes2");
//                                DBDepartment departObj = new DBDepartment();
//                                Department dep = departObj.findDepartment(empObj.getDept().getDnumber(), false);
//                                empObj.setDepartment(dep);
//                           
//                           
//                            }
//			}
//                        else{ //no employee was found
//                            cuObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cuObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT customerID, name, cAddress, zipcode, city, phoneNo From Customer ";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object
	private Customer buildCustomer(ResultSet results)
      {   Customer cuObj = new Customer();
          try{ // the columns from the table customer  are used
               cuObj.setCustomerID(results.getInt("customerID"));
                cuObj.setName(results.getString("name"));
                cuObj.setcAddress(results.getString("cAddress"));
                cuObj.setZipCode(results.getInt("zipcode"));
                cuObj.setCity(results.getString("city"));
                cuObj.setPhoneNo(results.getInt("phoneNo"));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return cuObj;
      }
	@Override
	public int Customer(ModelLayer.Customer cus) {
		// TODO Auto-generated method stub
		return 0;
	}
	@Override
	public ModelLayer.Customer searchCustomerID(int customerID, boolean b) {
		// TODO Auto-generated method stub
		return null;
	}

//	@Override
//	public Customer searchCustomerId(String ssn, boolean b) {
//		// TODO Auto-generated method stub
//		return null;
//	}
//	@Override
//	public ModelLayer.Customer findCustomer(int customerID,
//			boolean retriveAssociation) {
//		// TODO Auto-generated method stub
//		return null;
//	}
//	@Override
//	public int Customer(ModelLayer.Customer cu) {
//		// TODO Auto-generated method stub
//		return 0;
//	}
//	@Override
//	public ModelLayer.Customer searchCustomerID(int customerID, boolean b) {
//		// TODO Auto-generated method stub
//		return null;
//	}

 
}  
    



